package com.borqs.music.database;

import static com.borqs.music.MusicApplication.MD5_NULL;

import com.borqs.music.MusicApplication;
import com.borqs.music.model.SongInfo;
import com.borqs.music.util.AsyncLyricLoader;
import com.borqs.music.util.MyLogger;

import org.json.JSONException;
import org.json.JSONStringer;

import android.content.ContentResolver;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteQueryBuilder;
import android.net.Uri;
import android.provider.MediaStore.Audio;

import java.util.ArrayList;
import java.util.List;

public class DBController {
    private static final MyLogger logger = MyLogger.getLogger(DBController.class.getSimpleName());

    private static DBController instance;
    private static Context mContext;

    private MusicDatabaseHelper mHelper;
    private SQLiteDatabase mDB;

    private DBController(Context ctx) {
        mHelper = new MusicDatabaseHelper(ctx);
        mDB = mHelper.getWritableDatabase();
    }

    public static DBController getInstance(Context ctx) {
        mContext = ctx;

        if (instance == null) {
            instance = new DBController(ctx);
        }

        return instance;
    }

    public SongInfo queryNextUploadSong() {
        SongInfo song = new SongInfo();
        SQLiteQueryBuilder sqlbuilder = new SQLiteQueryBuilder();
        sqlbuilder.setTables(AudioTraceInfo.NAME);
        String[] projectionIn = {
                AudioTraceInfo.COLUMN_SONG_ID, AudioTraceInfo.COLUMN_SIZE,
                AudioTraceInfo.COLUMN_DATE_ADDED, AudioTraceInfo.COLUMN_MD5
        };

        String selection = AudioTraceInfo.COLUMN_IS_UPLOADED_SONG + " == 0";
        Cursor cursor = sqlbuilder.query(mDB, projectionIn, selection, null/* selectionArgs */,
                null/* groupBy */, null/* having */, null /* sortOrder */, "1");
        if (cursor == null) {
            logger.d("queryNextUploadSong(), cursor is null");
            return null;
        } else {
            if (cursor.moveToFirst()) {
                song.id = cursor.getInt(cursor.getColumnIndex(AudioTraceInfo.COLUMN_SONG_ID));
                song.fileSize = cursor.getLong(cursor.getColumnIndex(AudioTraceInfo.COLUMN_SIZE));
                song.dateAdded = cursor.getLong(cursor
                        .getColumnIndex(AudioTraceInfo.COLUMN_DATE_ADDED));
                song.fileMd5 = cursor.getString(cursor.getColumnIndex(AudioTraceInfo.COLUMN_MD5));
                cursor.close();
            } else {
                logger.d("Did not find next upload song!");
                return null;
            }
        }

        return song;
    }

    public SongInfo queryNextUploadLyric() {
        SongInfo song = new SongInfo();
        SQLiteQueryBuilder sqlbuilder = new SQLiteQueryBuilder();
        sqlbuilder.setTables(AudioTraceInfo.NAME);
        String[] projectionIn = {
                AudioTraceInfo.COLUMN_SONG_ID, AudioTraceInfo.COLUMN_SIZE,
                AudioTraceInfo.COLUMN_DATE_ADDED, AudioTraceInfo.COLUMN_MD5
        };

        String selection = AudioTraceInfo.COLUMN_IS_UPLOADED_LYRIC + " == 0";
        Cursor cursor = sqlbuilder.query(mDB, projectionIn, selection, null/* selectionArgs */,
                null/* groupBy */, null/* having */, null /* sortOrder */, "1");
        if (cursor == null) {
            logger.d("queryNextUploadLyric(), cursor is null");
            return null;
        } else {
            if (cursor.moveToFirst()) {
                song.id = cursor.getInt(cursor.getColumnIndex(AudioTraceInfo.COLUMN_SONG_ID));
                song.fileSize = cursor.getLong(cursor.getColumnIndex(AudioTraceInfo.COLUMN_SIZE));
                song.dateAdded = cursor.getLong(cursor
                        .getColumnIndex(AudioTraceInfo.COLUMN_DATE_ADDED));
                song.fileMd5 = cursor.getString(cursor.getColumnIndex(AudioTraceInfo.COLUMN_MD5));
                cursor.close();

                // Get song's lyric
                song.lyrics = AsyncLyricLoader.loadLyricFromLocal(song.fileUrl, song.artist
                        + song.title);
            } else {
                logger.d("Did not find next upload lyric!");
                return null;
            }
        }

        return song;
    }

    public SongInfo querySongByMD5(String fileMD5) {
        if (fileMD5 == null || MD5_NULL.equals(fileMD5)) {
            return null;
        }

        SongInfo song = new SongInfo();
        SQLiteQueryBuilder sqlbuilder = new SQLiteQueryBuilder();
        sqlbuilder.setTables(AudioTraceInfo.NAME);
        String[] projectionIn = {
                AudioTraceInfo.COLUMN_SONG_ID, AudioTraceInfo.COLUMN_SIZE,
                AudioTraceInfo.COLUMN_DATE_ADDED
        };

        String selection = AudioTraceInfo.COLUMN_MD5 + " = ?";
        String[] selectionArgs = {
            fileMD5
        };

        Cursor cursor = sqlbuilder.query(mDB, projectionIn, selection, selectionArgs,
                null/* groupBy */, null/* having */, null /* sortOrder */);
        if (cursor == null) {
            logger.d("querySongByMD5(), cursor is null");
            return null;
        } else {
            if (cursor.moveToFirst()) {
                song.id = cursor.getInt(cursor.getColumnIndex(AudioTraceInfo.COLUMN_SONG_ID));
                song.fileSize = cursor.getLong(cursor.getColumnIndex(AudioTraceInfo.COLUMN_SIZE));
                song.dateAdded = cursor.getLong(cursor
                        .getColumnIndex(AudioTraceInfo.COLUMN_DATE_ADDED));
            } else {
                logger.d("Did not find song by file md5!");
                return null;
            }
        }

        return song;
    }

    /**
     * Query all song from audio_info table
     *
     * @return
     */
    public List<SongInfo> queryAllSong() {
        SongInfo song;
        List<SongInfo> list;
        SQLiteQueryBuilder sqlbuilder = new SQLiteQueryBuilder();
        sqlbuilder.setTables(AudioTraceInfo.NAME);
        String[] projectionIn = {
                AudioTraceInfo.COLUMN_SONG_ID,
                AudioTraceInfo.COLUMN_DATE_ADDED,
                AudioTraceInfo.COLUMN_SIZE,
                AudioTraceInfo.COLUMN_MD5
        };

        Cursor cursor = sqlbuilder
                .query(mDB, projectionIn, null /* selection */, null /* selectionArgs */,
                        null/* groupBy */, null/* having */, null /* sortOrder */);
        if (cursor == null) {
            logger.d("querySongByMD5(), cursor is null");
            return null;
        } else {
            list = new ArrayList<SongInfo>();
            cursor.moveToFirst();
            while (!cursor.isAfterLast()) {
                song = new SongInfo();
                song.id = cursor.getInt(cursor.getColumnIndex(AudioTraceInfo.COLUMN_SONG_ID));
                song.fileSize = cursor.getLong(cursor.getColumnIndex(AudioTraceInfo.COLUMN_SIZE));
                song.dateAdded = cursor.getLong(cursor.getColumnIndex(AudioTraceInfo.COLUMN_DATE_ADDED));
                song.fileMd5 = cursor.getString(cursor.getColumnIndex(AudioTraceInfo.COLUMN_MD5));
                list.add(song);

                cursor.moveToNext();
            }
        }

        return list;
    }

    public long findSongIdByMD5(String md5) {
        SQLiteQueryBuilder sqlbuilder = new SQLiteQueryBuilder();
        sqlbuilder.setTables(AudioTraceInfo.NAME);
        String[] projectionIn = {
                AudioTraceInfo.COLUMN_SONG_ID, AudioTraceInfo.COLUMN_SIZE,
                AudioTraceInfo.COLUMN_DATE_ADDED, AudioTraceInfo.COLUMN_MD5
        };

        String selection = AudioTraceInfo.COLUMN_MD5 + " like '" + md5 +"'";
        Cursor cursor = sqlbuilder.query(mDB, projectionIn, selection, null/* selectionArgs */,
                null/* groupBy */, null/* having */, null /* sortOrder */, "1");

        long songId = -1;
        if (cursor != null && cursor.moveToFirst()) {
            songId = cursor.getLong(cursor.getColumnIndex(AudioTraceInfo.COLUMN_SONG_ID));
            cursor.close();
        }

        return songId;
    }

    public String findMD5BySongUrl(String songUrl) {
        SQLiteQueryBuilder sqlbuilder = new SQLiteQueryBuilder();
        sqlbuilder.setTables(AudioTraceInfo.NAME);
        String[] projectionIn = {
                AudioTraceInfo.COLUMN_SONG_ID, AudioTraceInfo.COLUMN_SIZE,
                AudioTraceInfo.COLUMN_DATE_ADDED, AudioTraceInfo.COLUMN_MD5
        };

        String selection = AudioTraceInfo.COLUMN_DATA + " = " + DatabaseUtils.sqlEscapeString(songUrl);
        Cursor cursor = sqlbuilder.query(mDB, projectionIn, selection, null/* selectionArgs */,
                null/* groupBy */, null/* having */, null /* sortOrder */, "1");

        String fileMD5 = MusicApplication.MD5_NULL;
        if (cursor != null && cursor.moveToFirst()) {
            fileMD5 = cursor.getString(cursor.getColumnIndex(AudioTraceInfo.COLUMN_MD5));
            cursor.close();
        }

        return fileMD5;
    }

    /**
     * Judge tracked song item exist or not by size/dataAdded-time.
     *
     * @param songId
     * @param size
     * @param dateAdded
     * @return boolean
     */
    private boolean isExistInMediaScannerTable(long songId, long size,
            long dateAdded) {
        Uri uri = Audio.Media.EXTERNAL_CONTENT_URI;
        String[] projectionIn = {
                Audio.Media.SIZE,
                Audio.Media.DATE_ADDED
        };
        String selection = Audio.Media._ID + " = ? ";
        String[] selectionArgs = {
                "" + songId
        };
        ContentResolver res = mContext.getContentResolver();
        Cursor cursor = res.query(uri, projectionIn, selection, selectionArgs, null);

        boolean found = false;
        if (cursor != null && cursor.moveToFirst()) {
            long audioSize = cursor.getLong(0);
            long audioDateAdded = cursor.getLong(1);

            found = ((audioSize == size) && (dateAdded == audioDateAdded));
        }

        if (cursor != null) {
            cursor.close();
        }

        return found;
    };

    /**
     * Check audio_info table whether exist needing to be uploaded songs.
     *
     * @return boolean
     */
    public boolean isExistNeedUploadSongs() {
        SQLiteQueryBuilder sqlbuilder = new SQLiteQueryBuilder();
        sqlbuilder.setTables(AudioTraceInfo.NAME);
        String selection = AudioTraceInfo.COLUMN_IS_UPLOADED_SONG + " == "
                + AudioTraceInfo.VALUE_OF_NOT_UPLOADED_SONG;

        Cursor cursor = sqlbuilder.query(mDB, null/* projectionIn */, selection,
                null/* selectionArgs */, null/* groupBy */, null/* having */, null /* sortOrder */,
                "1");

        if (cursor == null) {
            return false;
        } else {
            boolean retValue = (cursor.getCount() != 0);
            cursor.close();
            return retValue;
        }
    }

    public void markSongAsUploaded(String fileMd5) {
        ContentValues values = new ContentValues(1);
        values.put(AudioTraceInfo.COLUMN_IS_UPLOADED_SONG, 1);
        String whereClause = AudioTraceInfo.COLUMN_MD5 + " = ?";
        String[] whereArgs = {
            fileMd5
        };

        mDB.update(AudioTraceInfo.NAME, values, whereClause, whereArgs);
    }

    public void markLyricAsUploaded(String fileMd5) {
        ContentValues values = new ContentValues(1);
        values.put(AudioTraceInfo.COLUMN_IS_UPLOADED_LYRIC, 1);
        String whereClause = AudioTraceInfo.COLUMN_MD5 + " = ?";
        String[] whereArgs = {
            fileMd5
        };

        mDB.update(AudioTraceInfo.NAME, values, whereClause, whereArgs);
    }

    public void deleteStatisticsTable() {
        mDB.delete(StatisticsInfo.NAME, null, null);
    }

    /**
     * Delete invalid records from audio_info table.
     *
     * @return maxID
     */
    public long deleteInvalidRecords() {
        SQLiteQueryBuilder sqlbuilder = new SQLiteQueryBuilder();
        sqlbuilder.setTables(AudioTraceInfo.NAME);
        String[] projectionIn = {
                AudioTraceInfo.COLUMN_ID,
                AudioTraceInfo.COLUMN_SONG_ID,
                AudioTraceInfo.COLUMN_SIZE,
                AudioTraceInfo.COLUMN_DATE_ADDED
        };
        String sortOrder = AudioTraceInfo.COLUMN_ID + " ASC";
        Cursor cursor = sqlbuilder.query(mDB, projectionIn, null/* selection */,
                null/* selectionArgs */,
                null/* groupBy */, null/* having */, sortOrder);
        long maxID = -1;
        if (cursor != null) {
            while (cursor.moveToNext()) {
                long dbID = cursor.getLong(cursor.getColumnIndex(AudioTraceInfo.COLUMN_ID));
                long songId = cursor.getLong(cursor.getColumnIndex(AudioTraceInfo.COLUMN_SONG_ID));
                long size = cursor.getLong(cursor.getColumnIndex(AudioTraceInfo.COLUMN_SIZE));
                long dateAdded = cursor.getLong(cursor
                        .getColumnIndex(AudioTraceInfo.COLUMN_DATE_ADDED));

                boolean found = isExistInMediaScannerTable(songId, size, dateAdded);
                if (found) {
                    if (songId > maxID) {
                        maxID = songId;
                    }
                } else {
                    String whereClause = AudioTraceInfo.COLUMN_ID + " = ? ";
                    String[] selectionArgs = {
                            "" + dbID
                    };
                    mDB.delete(AudioTraceInfo.NAME, whereClause, selectionArgs);
                }
            }
            cursor.close();
        }

        return maxID;
    }

    public void updatePlayCount(String songMd5, String lyricsMd5) {
        SQLiteQueryBuilder sqlbuilder = new SQLiteQueryBuilder();
        sqlbuilder.setTables(StatisticsInfo.NAME);
        String[] projectionIn = {
                StatisticsInfo.COLUMN_LISTEN_COUNT
        };
        String selection = StatisticsInfo.COLUMN_SONG_MD5 + " = ? AND "
                + StatisticsInfo.COLUMN_LYRIC_MD5 + " = ?";
        String[] selectionArgs = {
                songMd5, lyricsMd5
        };

        Cursor cursor = sqlbuilder.query(mDB, projectionIn, selection,
                selectionArgs, null/* groupBy */, null/* having */, null /* sortOrder */, "1");
        if (null != cursor && cursor.moveToFirst()) {
            int playCount = cursor
                        .getInt(cursor.getColumnIndex(StatisticsInfo.COLUMN_LISTEN_COUNT));
            ContentValues values = new ContentValues(1);
            values.put(StatisticsInfo.COLUMN_LISTEN_COUNT, playCount + 1);
            mDB.update(StatisticsInfo.NAME, values, selection, selectionArgs);
        } else {
            ContentValues values = new ContentValues();
            values.clear();
            values.put(StatisticsInfo.COLUMN_SONG_MD5, songMd5);
            values.put(StatisticsInfo.COLUMN_LYRIC_MD5, lyricsMd5);
            values.put(StatisticsInfo.COLUMN_LISTEN_COUNT, 1);
            mDB.insert(StatisticsInfo.NAME, "0", values);
        }
        if (null != cursor){
            cursor.close();
        }
    }

    public void updateCorrectLyricCount(String songMd5, String lyricsMd5) {
        SQLiteQueryBuilder sqlbuilder = new SQLiteQueryBuilder();
        sqlbuilder.setTables(StatisticsInfo.NAME);
        String[] projectionIn = {
                StatisticsInfo.COLUMN_LYRIC_CORRECT
        };
        String selection = StatisticsInfo.COLUMN_SONG_MD5 + " = ? AND "
                + StatisticsInfo.COLUMN_LYRIC_MD5 + " = ?";
        String[] selectionArgs = {
                songMd5, lyricsMd5
        };

        Cursor cursor = sqlbuilder.query(mDB, projectionIn, selection,
                selectionArgs, null/* groupBy */, null/* having */, null /* sortOrder */, "1");
        if (null != cursor && cursor.moveToFirst()) {
            int correctCount = cursor
                        .getInt(cursor.getColumnIndex(StatisticsInfo.COLUMN_LYRIC_CORRECT));
            ContentValues values = new ContentValues(1);
            values.put(StatisticsInfo.COLUMN_LYRIC_CORRECT, correctCount + 1);
            mDB.update(StatisticsInfo.NAME, values, selection, selectionArgs);
        } else {
            ContentValues values = new ContentValues();
            values.clear();
            values.put(StatisticsInfo.COLUMN_SONG_MD5, songMd5);
            values.put(StatisticsInfo.COLUMN_LYRIC_MD5, lyricsMd5);
            values.put(StatisticsInfo.COLUMN_LYRIC_CORRECT, 1);
            mDB.insert(StatisticsInfo.NAME, "0", values);
        }
        if (null != cursor) {
            cursor.close();
        }
    }

    public void updateAllLyricsFlag(int flag) {
        ContentValues values = new ContentValues(1);
        values.put(AudioTraceInfo.COLUMN_IS_UPLOADED_LYRIC, flag);
        mDB.update(AudioTraceInfo.NAME, values, null/* whereClause */, null/* whereArgs */);
    }


    public String getStatisticsJSON() throws JSONException {
        SQLiteQueryBuilder sqlbuilder = new SQLiteQueryBuilder();
        sqlbuilder.setTables(StatisticsInfo.NAME);
        String[] projectionIn = {
                StatisticsInfo.COLUMN_ID,
                StatisticsInfo.COLUMN_SONG_MD5,
                StatisticsInfo.COLUMN_LYRIC_MD5,
                StatisticsInfo.COLUMN_LISTEN_COUNT,
                StatisticsInfo.COLUMN_LYRIC_CORRECT
        };
        Cursor cursor = sqlbuilder.query(mDB, projectionIn, null/* selection */,
                null/* selectionArgs */,
                null/* groupBy */, null/* having */, null/* sortOrder */);
        if (null == cursor || cursor.getCount() <= 0) {
            if (cursor != null) {
                cursor.close();
            }
            return null;
        }
        JSONStringer jsStringer = new JSONStringer();
        jsStringer.array();
        while (cursor.moveToNext()) {
            jsStringer.object();
            jsStringer.key("songmd5").value(
                        cursor.getString(cursor.getColumnIndex(StatisticsInfo.COLUMN_SONG_MD5)));
            jsStringer.key("lyricmd5").value(
                        cursor.getString(cursor.getColumnIndex(StatisticsInfo.COLUMN_LYRIC_MD5)));
            jsStringer.key("listencount").value(
                        cursor.getInt(cursor.getColumnIndex(StatisticsInfo.COLUMN_LISTEN_COUNT)));
            jsStringer.key("lyricerrorcount").value(
                        cursor.getInt(cursor.getColumnIndex(StatisticsInfo.COLUMN_LYRIC_CORRECT)));
            jsStringer.endObject();
        }
        cursor.close();
        jsStringer.endArray();

        return jsStringer.toString();
    }


     /********************************************************
     * Cache song                                            *
     * ******************************************************/

    public void insertAllCachedSong(List<SongInfo> songs, int cacheSongType) {
        if (songs == null || songs.size() == 0) {
            return;
        }

        String whereClause = SongCacheInfo.COLUMN_CACHE_TYPE + " = " + cacheSongType;
        // delete old cache
        mDB.delete(SongCacheInfo.NAME, whereClause, null);

        mDB.beginTransaction();
        try {
            ContentValues values = new ContentValues();
            for (SongInfo song : songs) {
                values.clear();
                values.put(SongCacheInfo.COLUMN_SONG_ID, song.id);
                values.put(SongCacheInfo.COLUMN_FILE_NAME, song.fileName);
                values.put(SongCacheInfo.COLUMN_FILE_TYPE, song.fileType);
                values.put(SongCacheInfo.COLUMN_FILE_URL, song.fileUrl);
                values.put(SongCacheInfo.COLUMN_FILE_SIZE, song.fileSize);
                values.put(SongCacheInfo.COLUMN_FILE_MD5, song.fileMd5);
                values.put(SongCacheInfo.COLUMN_TITLE, song.title);
                values.put(SongCacheInfo.COLUMN_ARTIST, song.artist);
                values.put(SongCacheInfo.COLUMN_ALBUM, song.album);
                values.put(SongCacheInfo.COLUMN_YEAR, song.year);
                values.put(SongCacheInfo.COLUMN_GENRE, song.genre);
                values.put(SongCacheInfo.COLUMN_LYRICS, song.lyrics);
                values.put(SongCacheInfo.COLUMN_IMG_URL, song.imgUrl);
                values.put(SongCacheInfo.COLUMN_IS_FAVORITE, song.isFavorite);
                values.put(SongCacheInfo.COLUMN_IS_NOT_IN_DEVICE, song.isNotInDevice);
                values.put(SongCacheInfo.COLUMN_IS_PUBLIC_SONG, song.isPublicSong);
                values.put(SongCacheInfo.COLUMN_BITRATE, song.bitrate);
                values.put(SongCacheInfo.COLUMN_DURATION, song.duration);
                values.put(SongCacheInfo.COLUMN_CACHE_TYPE, cacheSongType);

                mDB.insert(SongCacheInfo.NAME, "0", values);
            }
            mDB.setTransactionSuccessful();
        } finally {
            mDB.endTransaction();
        }

    }

    public void deleteCachedSong(int songId, int songCacheType) {
        if (songId < 0) {
            return;
        }

        String whereClause = SongCacheInfo.COLUMN_SONG_ID + " = " + songId;
        // delete old cache
        mDB.delete(SongCacheInfo.NAME, whereClause, null);
    }

    public int queryCachedSongSize(int songCacheType) {
        int cachedSongSize = 0;
        SQLiteQueryBuilder sqlbuilder = new SQLiteQueryBuilder();
        sqlbuilder.setTables(SongCacheInfo.NAME);

        String selection = SongCacheInfo.COLUMN_CACHE_TYPE + " = " + songCacheType;
        Cursor cursor = sqlbuilder.query(mDB, null /* projectionIn */, selection,
                null/* selectionArgs */,
                null/* groupBy */, null/* having */, null/* sortOrder */);

        if (cursor != null) {
            cachedSongSize = cursor.getCount();
            cursor.close();
        }

        return cachedSongSize;
    }

    public List<SongInfo> queryAllCahcedSong(int songCacheType) {
        SQLiteQueryBuilder sqlbuilder = new SQLiteQueryBuilder();
        sqlbuilder.setTables(SongCacheInfo.NAME);

        String selection = SongCacheInfo.COLUMN_CACHE_TYPE + " = " + songCacheType;
        Cursor cursor = sqlbuilder.query(mDB, null /* projectionIn */, selection,
                null/* selectionArgs */,
                null/* groupBy */, null/* having */, null/* sortOrder */);

        List<SongInfo> songs = transformCachedSongToList(cursor);

        if (cursor != null) {
            cursor.close();
        }
        return songs;
    }

    public List<SongInfo> queryCachedSongByArtist(String artistKey, int songCacheType) {
        SQLiteQueryBuilder sqlbuilder = new SQLiteQueryBuilder();
        sqlbuilder.setTables(SongCacheInfo.NAME);

        String selection = SongCacheInfo.COLUMN_CACHE_TYPE + " = " + songCacheType + " and "
                + SongCacheInfo.COLUMN_ARTIST + " like " + "'" + artistKey + "'";
        Cursor cursor = sqlbuilder.query(mDB, null /* projectionIn */, selection,
                null/* selectionArgs */,
                null/* groupBy */, null/* having */, null/* sortOrder */);

        List<SongInfo> songs = transformCachedSongToList(cursor);

        if (cursor != null) {
            cursor.close();
        }
        return songs;
    }

    public List<SongInfo> queryCachedSongByAlbum(String albumKey, int songCacheType) {
        SQLiteQueryBuilder sqlbuilder = new SQLiteQueryBuilder();
        sqlbuilder.setTables(SongCacheInfo.NAME);

        String selection = SongCacheInfo.COLUMN_CACHE_TYPE + " = " + songCacheType + " and "
                + SongCacheInfo.COLUMN_ALBUM + " like " + "'" + albumKey + "'";
        Cursor cursor = sqlbuilder.query(mDB, null /* projectionIn */, selection,
                null/* selectionArgs */,
                null/* groupBy */, null/* having */, null/* sortOrder */);

        List<SongInfo> songs = transformCachedSongToList(cursor);

        if (cursor != null) {
            cursor.close();
        }
        return songs;
    }

    public List<SongInfo> queryCachedSongByFavor(int songCacheType) {

        SQLiteQueryBuilder sqlbuilder = new SQLiteQueryBuilder();
        sqlbuilder.setTables(SongCacheInfo.NAME);

        String selection = SongCacheInfo.COLUMN_CACHE_TYPE + " = " + songCacheType + " and "
                + SongCacheInfo.COLUMN_IS_FAVORITE + " = 1";
        Cursor cursor = sqlbuilder.query(mDB, null /* projectionIn */, selection,
                null/* selectionArgs */,
                null/* groupBy */, null/* having */, null/* sortOrder */);

        List<SongInfo> songs = transformCachedSongToList(cursor);

        if (cursor != null) {
            cursor.close();
        }
        return songs;

    }

    public int updateCachedSongFavor(boolean isFavor, String fileMD5, int songCacheType) {
        String selection = SongCacheInfo.COLUMN_CACHE_TYPE + " = " + songCacheType + " and "
                + SongCacheInfo.COLUMN_FILE_MD5 + " like '" + fileMD5 + "'" ;
        ContentValues values = new ContentValues();
        values.put(SongCacheInfo.COLUMN_IS_FAVORITE, isFavor ? 1 : 0);

        return mDB.update(SongCacheInfo.NAME, values, selection, null);
    }

    private List<SongInfo> transformCachedSongToList(Cursor cacheSongCursor) {
        ArrayList<SongInfo> songs = new ArrayList<SongInfo>();
        Cursor cursor = cacheSongCursor;

        if (cursor != null) {
            int idIndex = cursor.getColumnIndexOrThrow(SongCacheInfo.COLUMN_SONG_ID);
            int fileNameIndex = cursor.getColumnIndexOrThrow(SongCacheInfo.COLUMN_FILE_NAME);
            int fileTypeIndex = cursor.getColumnIndexOrThrow(SongCacheInfo.COLUMN_FILE_TYPE);
            int fileUrlIndex = cursor.getColumnIndexOrThrow(SongCacheInfo.COLUMN_FILE_URL);
            int fileSizeIndex = cursor.getColumnIndexOrThrow(SongCacheInfo.COLUMN_FILE_SIZE);
            int fileMD5 = cursor.getColumnIndexOrThrow(SongCacheInfo.COLUMN_FILE_MD5);
            int titleIndex = cursor.getColumnIndexOrThrow(SongCacheInfo.COLUMN_TITLE);
            int artistIndex = cursor.getColumnIndexOrThrow(SongCacheInfo.COLUMN_ARTIST);
            int albumIndex = cursor.getColumnIndexOrThrow(SongCacheInfo.COLUMN_ALBUM);
            int yearIndex = cursor.getColumnIndexOrThrow(SongCacheInfo.COLUMN_YEAR);
            int genreIndex = cursor.getColumnIndexOrThrow(SongCacheInfo.COLUMN_GENRE);
            int lyricsIndex = cursor.getColumnIndexOrThrow(SongCacheInfo.COLUMN_LYRICS);
            int imgUrlIndex = cursor.getColumnIndexOrThrow(SongCacheInfo.COLUMN_IMG_URL);
            int isFavoriteIndex = cursor
                    .getColumnIndexOrThrow(SongCacheInfo.COLUMN_IS_FAVORITE);
            int isNotInDeviceIndex = cursor
                    .getColumnIndexOrThrow(SongCacheInfo.COLUMN_IS_NOT_IN_DEVICE);
            int isPublicSongIndex = cursor
                    .getColumnIndexOrThrow(SongCacheInfo.COLUMN_IS_PUBLIC_SONG);
            int bitrateIndex = cursor.getColumnIndexOrThrow(SongCacheInfo.COLUMN_BITRATE);
            int durationIndex = cursor.getColumnIndexOrThrow(SongCacheInfo.COLUMN_DURATION);

            while (cursor.moveToNext()) {
                SongInfo song = new SongInfo();
                song.id = cursor.getInt(idIndex);
                song.fileName = cursor.getString(fileNameIndex);
                song.fileType = cursor.getString(fileTypeIndex);
                song.fileUrl = cursor.getString(fileUrlIndex);
                song.fileSize = cursor.getLong(fileSizeIndex);
                song.fileMd5 = cursor.getString(fileMD5);

                // ID3 Tags
                song.title = cursor.getString(titleIndex);
                song.artist = cursor.getString(artistIndex);
                song.album = cursor.getString(albumIndex);
                song.year = cursor.getString(yearIndex);
                song.genre = cursor.getString(genreIndex);
                song.lyrics = cursor.getString(lyricsIndex);
                song.imgUrl = cursor.getString(imgUrlIndex);
                song.isFavorite = (cursor.getInt(isFavoriteIndex) == 1);
                song.isNotInDevice = (cursor.getInt(isNotInDeviceIndex) == 1);
                song.isPublicSong = (cursor.getInt(isPublicSongIndex) == 1);

                song.bitrate = cursor.getInt(bitrateIndex);
                song.duration = cursor.getInt(durationIndex);

                songs.add(song);
            }
        }

        return songs;
    }

}

